﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Entities;

namespace DataAccess
{
	[Serializable]
	public class GenreDAL
	{
		private string connectionString = "Data Source=" + new System.IO.DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DoubleFeature").FullName + "\\Resources\\movies.sqlite";

		public bool Save(Genre entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO genres (genre_name) VALUES (?)", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Update(Genre entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE genres SET genre_name=? WHERE genre_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Delete(Genre entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM genres WHERE genre_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Id;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

                cmd.CommandText = "DELETE FROM relationships WHERE relationship_element=" + entity.Id + " AND relationship_type='genre'";

                cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public List<Genre> List()
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);
			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM genres ORDER BY genre_name ASC", conn);

			conn.Open();

			List<Genre> collection = new List<Genre>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				Genre obj = new Genre();

				obj.Id = Convert.ToInt32(dr["genre_id"]);
				obj.Name = dr["genre_name"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}

		public Genre GetByName(string name)
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);
			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM genres WHERE genre_name=?", conn);

			SQLiteParameter param1 = new SQLiteParameter();
			param1.Value = name;

			cmd.Parameters.Add(param1);

			conn.Open();

			Genre entity = new Genre();

			SQLiteDataReader dr = cmd.ExecuteReader();

			if (dr.HasRows)
			{
				dr.Read();

				entity.Id = Convert.ToInt32(dr["genre_id"]);
				entity.Name = dr["genre_name"].ToString();

				conn.Close();

				return entity;
			}
			else
			{
				conn.Close();

				return null;
			}
		}

		public Genre GetLastAdded()
		{
			Genre entity = new Genre();

			SQLiteConnection conn = new SQLiteConnection(connectionString);

			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM genres ORDER BY genre_id DESC LIMIT 1", conn);

			conn.Open();

			SQLiteDataReader dr = cmd.ExecuteReader();

			dr.Read();

			entity.Id = Convert.ToInt32(dr["genre_id"]);
			entity.Name = dr["genre_name"].ToString();

			conn.Close();

			return entity;
		}
	}
}
